package DBLayer;
import ModelLayer.OrderHistory;
import java.sql.*;
import java.util.ArrayList;

public class DBOrderHistory {
    // <editor-fold defaultstate="collapsed" desc="Attributes & Constructors">
    private Connection conn;

    public DBOrderHistory() {
        conn = SQLConnector.getInstance().getConnection();
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL SELECT Methods">
    public OrderHistory getOrderHistoryByID(int id) {
        return selectSingle("SELECT `id_order_history`, `id_employee`, `id_order`, `id_order_state`, UNIX_TIMESTAMP(`date_add`) AS `date_add` FROM `ps_order_history` WHERE `id_order_history` = " + id + " LIMIT 0, 1;");
    }

    public ArrayList<OrderHistory> getOrderHistoryList() {
        return selectMultiple("SELECT `id_order_history`, `id_employee`, `id_order`, `id_order_state`, UNIX_TIMESTAMP(`date_add`) AS `date_add` FROM `ps_order_history` ORDER BY `id_order_history` ASC;");
    }

    private OrderHistory selectSingle(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        OrderHistory orderHistory = new OrderHistory();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            if (result.next()) {
                orderHistory = buildOrderHistory(result);
            }
            System.out.println("Fetching OrderHistory Object");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return orderHistory;
    }

    private ArrayList<OrderHistory> selectMultiple(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ArrayList<OrderHistory> orderHistories = new ArrayList<OrderHistory>();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            while (result.next()) {
                OrderHistory orderHistory = new OrderHistory();
                orderHistory = buildOrderHistory(result);
                orderHistories.add(orderHistory);
            }
            System.out.println("Fetching OrderHistory List");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return orderHistories;
    }

    private OrderHistory buildOrderHistory(ResultSet result) {
        OrderHistory orderHistory = new OrderHistory();
        try {
            orderHistory.setIDOrderHistory(result.getInt("id_order_history"));
            orderHistory.setIDEmployee(result.getInt("id_employee"));
            orderHistory.setIDOrder(result.getInt("id_order"));
            orderHistory.setIDOrderState(result.getInt("id_order_state"));
            orderHistory.setDateAdd(result.getInt("date_add"));

        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
        return orderHistory;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL INSERT, UPDATE & DELETE Methods">
    public int addOrderHistory(OrderHistory orderHistory) {
        int returnID = 0;
        Statement stmt = null;
        ResultSet result = null;

        String query = "INSERT INTO `ps_order_history` (`id_order_history`, `id_employee`, `id_order`, `id_order_state`, `date_add`) "
                + "VALUES (" + orderHistory.getIDOrderHistory() + ", " + orderHistory.getIDEmployee() + ", " + orderHistory.getIDOrder() + ", " + orderHistory.getIDOrderState() + ", FROM_UNIXTIME(" + orderHistory.getDateAdd() + "));";

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
            result = stmt.getGeneratedKeys();
            if (result.next()) {
                returnID = result.getInt(1);
            }
            System.out.println("Added OrderHistory ID " + returnID);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public int updateOrderHistory(OrderHistory orderHistory, int id) {
        int returnID = 0;
        Statement stmt = null;
        String query = "UPDATE `ps_order_history` SET "
                + "`id_order_history`       = "  + orderHistory.getIDOrderHistory() + ", "
                + "`id_employee`            = "  + orderHistory.getIDEmployee() + ", "
                + "`id_order`               = "  + orderHistory.getIDOrder() + ", "
                + "`id_order_state`         = "  + orderHistory.getIDOrderState() + ", "
                + "`date_add`               = FROM_UNIXTIME("  + orderHistory.getDateAdd() + ") "
                + "WHERE `id_order_history` = " + id + " LIMIT 1;";

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query);
            returnID = orderHistory.getIDOrderHistory();
            System.out.println("Updated OrderHistory ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public void deleteOrderHistory(int id) {
        Statement stmt = null;

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate("DELETE FROM `ps_order_history` WHERE `id_order_history` = " + id + " LIMIT 1;");
            System.out.println("Deleted OrderHistory ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
    }
    // </editor-fold>
}
